﻿using System;
using SmartSiteManagementSystem.IRepository.Admin;
using SmartSiteManagementSystem.Models.Admin;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace SmartSiteManagementSystem.Repository.Admin
{
    public class AdminUserInfoRepository : IAdminUserInfoRepository
    //,IDisposable
    {
        #region dataTable
        /*
        create table `admin_user`(
	`user_id` int not null auto_increment primary key,
	`user_name` varchar(50) not null,
	`user_pwd` varchar(50) not null,
	`user_type` tinyint  not null,
    `email` varchar(50) null,
    `hand_mobile` varchar(50) null,
	`create_time` datetime not null default now(),
	`update_time` datetime not null default now(),
	`is_stop` bit not null default 0,
	`is_deleted`  bit not null default 0,
	`emark` varchar(250) null,
	`idcard` varchar(50) null
) default charset=utf8;
);
         */
        #endregion
       // private IDbConnection _readConn;
       // private IDbConnection _writeConn;
        /// <summary>
        /// 取得记录数
        /// </summary>
        /// <param name="param">可选参数为：</param>
        /// <returns></returns>
        public int GetCount(Hashtable param)
        {
            int back = 0;
            if (param == null)
                param = new Hashtable();
            String querySql = "select count(user_id) from admin_user";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("user_id"))
                    {
                        pars.Add("user_id", param["user_id"]);
                        whereCondition.Append(" and user_id =@user_id");
                    }
                    if (param.ContainsKey("user_name"))
                    {
                        pars.Add("user_name", param["user_name"]);
                        whereCondition.Append(" and user_name =@user_name");
                    }
                    if (param.ContainsKey("user_type"))
                    {
                        pars.Add("user_type", param["user_type"]);
                        whereCondition.Append(" and user_type =@user_type");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }

                }
               
                string execSql = querySql + whereCondition.ToString();
                #endregion
                back = _readConn.QueryFirstOrDefault<int>(execSql, pars);
            }
            return back;
        }

        /// <summary>
        /// 提取数据
        /// </summary>
        /// <param name="param">可选参数为：</param>
        /// <param name="orderBy">排序方式:默认为“GroupID asc”</param>
        /// <param name="first">起始记录：从0开始</param>
        /// <param name="rows">提取的条数</param>
        /// <returns></returns>
        public IList<AdminUserInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {
            IList<AdminUserInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select user_id as AdminUserId,user_name as UserName,user_pwd as UserPwd,user_type as UserType,email as Email,hand_mobile as HandMobile,create_time as CreateTime,update_time as UpdateTime,is_stop as StopFlag,is_deleted as DeleteFlag,remark as Remark,idcard as IdCard from admin_user";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("user_id"))
                    {
                        pars.Add("user_id", param["user_id"]);
                        whereCondition.Append(" and user_id =@user_id");
                    }
                    if (param.ContainsKey("user_name"))
                    {
                        pars.Add("user_name", param["user_name"]);
                        whereCondition.Append(" and user_name =@user_name");
                    }
                    if (param.ContainsKey("user_type"))
                    {
                        pars.Add("user_type", param["user_type"]);
                        whereCondition.Append(" and user_type =@user_type");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by user_id desc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<AdminUserInfo>(execSql, pars).AsList<AdminUserInfo>();
            }
            return back;

        }

        /// <summary>
        /// 提取数据
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public AdminUserInfo Get(Hashtable param)

        {
            AdminUserInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select user_id as AdminUserId,user_name as UserName,user_pwd as UserPwd,user_type as UserType,email as Email,hand_mobile as HandMobile,create_time as CreateTime,update_time as UpdateTime,is_stop as StopFlag,is_deleted as DeleteFlag,remark as Remark,idcard as IdCard from admin_user";
        using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
        {

            DynamicParameters pars = new DynamicParameters();
            string whereCondition = "";
            if (param.Count > 0)
            {
                whereCondition = " where 1=1";
                if (param.ContainsKey("user_id"))
                {
                    pars.Add("user_id", param["user_id"]);
                    whereCondition = whereCondition + " and user_id =@user_id";
                }
                if (param.ContainsKey("user_name"))
                {
                    pars.Add("user_name", param["user_name"]);
                    whereCondition = whereCondition + " and user_name =@user_name";
                }
                if (param.ContainsKey("user_type"))
                {
                    pars.Add("user_type", param["user_type"]);
                    whereCondition = whereCondition + " and user_type =@user_type";
                }
                if (param.ContainsKey("is_stop"))
                {
                    pars.Add("is_stop", param["is_stop"]);
                    whereCondition = whereCondition + " and is_stop =@is_stop";
                }
            }
            back = _readConn.QueryFirstOrDefault<AdminUserInfo>(querySql + whereCondition, pars);
        }
            return back;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="obj">对象</param>
        /// <returns>返回：该条数据的主键Id</returns>
        public int Insert(AdminUserInfo obj)
        {
           
            int back = 0;
            string sql = @"insert into admin_user (
	  user_name
	 ,user_pwd
	 ,user_type
     ,email
     ,hand_mobile
	 ,create_time
	 ,update_time
	 ,is_stop
	 ,is_deleted
	 ,remark
	 ,idcard
      ) VALUES(
      @user_name
      , @user_pwd
      , @user_type
      , @email
      , @hand_mobile
      , @create_time
      , @update_time
      , @is_stop
      , @is_deleted
      , @remark
      , @idcard
      );select last_insert_id();";

            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {
             
                int backint = _writeConn.QueryFirst<int>(sql, new { user_name = obj.UserName
                                                            ,user_pwd=obj.UserPwd
                                                            ,user_type=obj.UserType
                                                            ,email=obj.Email
                                                            ,hand_mobile=obj.HandMobile
                                                            ,create_time=obj.CreateTime
                                                            ,update_time=obj.UpdateTime
                                                            ,is_stop=obj.StopFlag==true?1:0
                                                            ,is_deleted=obj.DeleteFlag==true?1:0
                                                            ,remark =obj.Remark
                                                            ,idcard=obj.IdCard });
                back = backint;
            }
            return back;
        }

        public bool Update(AdminUserInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @" UPDATE admin_user
 SET
user_name = @user_name,
user_pwd = @user_pwd,
user_type = @user_type,
email = @email,
hand_mobile= @hand_mobile,
create_time = @create_time,
update_time = @update_time,
is_stop = @is_stop,
is_deleted = @is_deleted,
remark = @remark,
idcard = @idcard
WHERE user_id =@user_id;";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                int backint = _writeConn.Execute(sql, new
                {
                    user_id = obj.AdminUserId,
                    user_name = obj.UserName,
                    user_pwd = obj.UserPwd,
                    user_type=obj.UserType,
                    email = obj.Email,
                    hand_mobile = obj.HandMobile,
                    create_time = obj.CreateTime,
                    update_time=obj.UpdateTime,
                    is_stop = obj.StopFlag,
                    is_deleted = obj.DeleteFlag,
                    remark = obj.Remark,
                    idcard = obj.IdCard
                });
                if (backint > 0)
                {
                    back = true;
                }
            }
            return back;
        }

        public bool Delete(Hashtable param)
        {
            throw new NotImplementedException();
        }

    }

    /// <summary>
    /// 更新数据
    /// </summary>
    /// <param name="obj"></param>
    /// <returns>返回：ture 成功，false 失败</returns>
    //public bool Update(AdminUserInfo obj)
    //{
    //    if (obj == null) throw new ArgumentNullException("obj");
    //    String stmtId = "TAdminUser.Update";
    //    int result = MainMapper.Instance().QueryForObject<int>(stmtId, obj);
    //    return result > 0 ? true : false;
    //}

    /// <summary>
    /// 删除数据
    /// </summary>
    /// <param name="param"></param>
    /// <returns>返回：ture 成功，false 失败</returns>
    //public bool Delete(Hashtable param)
    //{

    //    if (param == null) throw new ArgumentNullException("param");

    //    int result=_readConn.Execute("delete from admin_user where id=@ID", Area);
    //    return result > 0 ? true : false;
    //}
    //public void TestDapperTransaction()
    //{
    //    using (var conn = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;Database=test;User Id=root;password=root"))
    //    {
    //        conn.Open();
    //        //开启事务
    //        IDbTransaction trans = conn.BeginTransaction();
    //        try
    //        {
    //            int row = conn.Execute(@"update t set name='www.lanhusoft.com' where id=@id", new { id = 3 }, trans);
    //            row += conn.Execute("delete from t where id=@id", new { id = 5 }, trans);
    //            for (int i = 0; i < 100; i++)
    //            {
    //                conn.Execute(@"insert t(id, name) values (@id, @name)", new { id = i, name = "www.lanhusoft.com/" + i });
    //            }
    //            trans.Commit(); //提交事务
    //            conn.Close();
    //        }
    //        catch (Exception)
    //        {
    //            trans.Rollback(); //回滚事务
    //            conn.Close();
    //        }
    //    }
    // }

}

